﻿CREATE PROCEDURE [dbo].[sp_Insert_OrderedLeads]
	@PeroidMonth varchar(2),
	@PeroidYear varchar(4)
AS
	truncate table dbo.OrderedLeads
	
	insert into dbo.OrderedLeads
	select l.LeadID, cast(l.RawData as varchar(max)) + (case when zi.AgentName is null then '' else zi.AgentName end) 
	from dbo.Orders o
	inner join dbo.OrderZip oz on o.OrderID = oz.OrderID
	inner join dbo.ZipInfo zi on oz.ZipID = zi.ZipID
	inner join dbo.Zips z on z.ZipID = oz.ZipID
	inner join dbo.Leads l on l.ZipRequesterCode = z.Zip
	where PeroidMonth = @PeroidMonth and PeroidYear = @PeroidYear and IsPurchased = 1
	and ((oz.Type = 1 and l.LeadType <> 'B')
	or (oz.Type = 2 and l.LeadType = 'B')
	or oz.Type = 0)